/*package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import org.json.JSONArray;

import fy.config.FyConfig;
import fy.uap.tree.NodeBean;
import fy.uap.tree.RootBean;
import fy.util.DBUtil;

public class QueryProviderInfoAndRangeByCodeDao {

	public static JSONObject queryProviderInfoAndRange(String code,
			String accountID) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		short state = 1;

		JSONObject jo = new JSONObject();
		List<String> qualifiedSupplyRangeList = null;
		List<String> potentialSupplyRangeList = null;
		List<String> stopSupplyRangeList = null;

		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());


			pst = conn
					.prepareStatement("select cvenname from fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null) and (denddate is null or to_date(denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ))");

			pst.setString(1, code);

			rs = pst.executeQuery();

			if (rs.next()) {
				jo.append("companyName", rs.getString("cvenname"));

				DBUtil.close(rs);
				DBUtil.close(pst);
				state = 2;
				pst = conn
						.prepareStatement("select i.pk_invclass, i.cvendorclass from fy_supplier_vendor_invclass i, fy_supplier_vendor v where (i.dr='0' or i.dr is null) and i.pk_vendor=v.pk_vendor and v.cvencode = ? and trim(i.pk_corp) = ? and i.cvendorclass<>'3' and ( (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )))");

				pst.setString(1, code);
				pst.setString(2, accountID);

				rs = pst.executeQuery();
				while (rs.next()) {
					String cvendorclass = rs.getString(2);
					if ("1".equals(cvendorclass)) {
						if (qualifiedSupplyRangeList == null) {
							qualifiedSupplyRangeList = new ArrayList<String>();
						}
						qualifiedSupplyRangeList.add(rs.getString(1));
					} else if ("2".equals(cvendorclass)) {
						if (potentialSupplyRangeList == null) {
							potentialSupplyRangeList = new ArrayList<String>();
						}
						potentialSupplyRangeList.add(rs.getString(1));
					} else if ("3".equals(cvendorclass)) {
						if (stopSupplyRangeList == null) {
							stopSupplyRangeList = new ArrayList<String>();
						}
						stopSupplyRangeList.add(rs.getString(1));
					}
					state = 3;
				}
			}

		} catch (Exception e) {
			state = 4;
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		try {
			jo.put("state", state);
			jo.put("qualifiedSupplyRange",
					getRangeString(qualifiedSupplyRangeList));
			jo.put("potentialSupplyRange",
					getRangeString(potentialSupplyRangeList));
			// jo.put("stopSuppliers", getRangeString(stopSupplyRangeList));
		} catch (JSONException e) {
			e.printStackTrace();
		}

		// System.out.println(jo.toString());
		return jo;
	}

	public static String getProviderInfoAndRangeXMLByCode(String code,
			String accountID) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		JSONObject jo = new JSONObject();

		RootBean qualifiedSupplyRangeRootBean = new RootBean();
		RootBean potentialSupplyRangeRootBean = new RootBean();
		Map<String, List<NodeBean>> qualifiedSupplyRangeMap = new HashMap<String, List<NodeBean>>();
		Map<String, List<NodeBean>> potentialSupplyRangeMap = new HashMap<String, List<NodeBean>>();
		JSONObject jtgkJSON = null;
		short state = 1;

		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			pst = conn
					.prepareStatement("select cvenname from fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null) and (denddate is null or to_date(denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ) or cvencode = 'WFQJS01')");

			pst.setString(1, code);

			rs = pst.executeQuery();

			if (rs.next()) {
				jo.append("companyName", rs.getString("cvenname"));

				DBUtil.close(rs);
				DBUtil.close(pst);
				state = 2;
				pst = conn
						.prepareStatement("select i.pk_invclass,f.cinvclassname, i.cvendorclass, f1.pk_invclass, f1.cinvclassname, f2.pk_invclass, f2.cinvclassname,f1.binvgroupdot from fy_supplier_vendor_invclass i left join  fy_supplier_vendor v on i.pk_vendor=v.pk_vendor left join fy_supplier_invclass f on i.pk_invclass = f.pk_invclass left join fy_supplier_invclass f1 on f.pk_invfather = f1.pk_invclass left join fy_supplier_invclass f2 on f1.pk_invfather = f2.pk_invclass where (i.dr='0' or i.dr is null) and trim(i.pk_corp) = ? and v.cvencode = ? and i.cvendorclass<>'3' and ( (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )))");
				pst.setString(1, accountID);
				pst.setString(2, code);
				

				rs = pst.executeQuery();
				jtgkJSON = new JSONObject();
				while (rs.next()) {
					String cvendorclass = rs.getString(3);
					jtgkJSON.put(rs.getString(1), rs.getString(8));
					if ("1".equals(cvendorclass)) {

						// i.pk_invclass,f.cinvclassname, i.cvendorclass,
						// f1.pk_invclass, f1.cinvclassname, f2.pk_invclass,
						// f2.cinvclassname
						NodeBean bean1 = new NodeBean();
						bean1.setId(rs.getString(1));
						bean1.setText(rs.getString(2));
						String tempID = rs.getString(4);
						
						if (tempID != null && !"".equals(tempID)) {
							putNode(qualifiedSupplyRangeMap, tempID, bean1);
							NodeBean bean2 = new NodeBean();
							bean2.setId(tempID);
							bean2.setText(rs.getString(5));
							bean2.setDisabled(true);
							tempID = rs.getString(6);
							if (tempID != null && !"".equals(tempID)) {
								putNode(qualifiedSupplyRangeMap, tempID, bean2);
								NodeBean bean3 = new NodeBean();
								bean3.setId(tempID);
								bean3.setText(rs.getString(7));
								bean3.setDisabled(true);
								qualifiedSupplyRangeRootBean.addItem(bean3);
							} else {
								qualifiedSupplyRangeRootBean.addItem(bean2);
							}
						} else {
							qualifiedSupplyRangeRootBean.addItem(bean1);
						}
					} else if ("2".equals(cvendorclass)) {
						NodeBean bean1 = new NodeBean();
						bean1.setId(rs.getString(1));
						bean1.setText(rs.getString(2));
						String tempID = rs.getString(4);
						if (tempID != null && !"".equals(tempID)) {
							putNode(potentialSupplyRangeMap, tempID, bean1);
							NodeBean bean2 = new NodeBean();
							bean2.setId(tempID);
							bean2.setText(rs.getString(5));
							bean2.setDisabled(true);
							tempID = rs.getString(6);
							if (tempID != null && !"".equals(tempID)) {
								putNode(potentialSupplyRangeMap, tempID, bean2);
								NodeBean bean3 = new NodeBean();
								bean3.setId(tempID);
								bean3.setText(rs.getString(7));
								bean3.setDisabled(true);
								potentialSupplyRangeRootBean.addItem(bean3);
							} else {
								potentialSupplyRangeRootBean.addItem(bean2);
							}
						} else {
							potentialSupplyRangeRootBean.addItem(bean1);
						}
					}
					state = 3;
				}
				potentialSupplyRangeRootBean.addItems(potentialSupplyRangeMap);
				qualifiedSupplyRangeRootBean.addItems(qualifiedSupplyRangeMap);
			}

		} catch (Exception e) {
			state = 4;
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		try {
			jo.put("state", state);
			jo.put("qualifiedSupplyRange", qualifiedSupplyRangeRootBean
					.toXMLElement().asXML());
			jo.put("potentialSupplyRange", potentialSupplyRangeRootBean
					.toXMLElement().asXML());
			jo.put("jtgk", jtgkJSON);
			// jo.put("stopSuppliers", getRangeString(stopSupplyRangeList));
		} catch (JSONException e) {
			e.printStackTrace();
		}

		return jo.toString();
	}

	private static JSONArray getRangeString(List<String> list) {
		JSONArray ja = new JSONArray();
		if (list != null) {
			for (int i = 0, size = list.size(); i < size; i++) {
				ja.put(list.get(i));
			}
		}
		return ja;
	}
	
	private static void putNode(Map<String, List<NodeBean>> nodeMap,String parentPK, NodeBean node) {
		List<NodeBean> list = nodeMap.get(parentPK);
		if (list == null) {
			list = new ArrayList<NodeBean>();
			nodeMap.put(parentPK, list);
		}
		list.add(node);
	}
}
*/
package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import fy.mdm.dao.Providerdao;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import org.json.JSONArray;

import fy.config.FyConfig;
import fy.uap.tree.NodeBean;
import fy.uap.tree.RootBean;
import fy.util.DBUtil;

public class QueryProviderInfoAndRangeByCodeDao {
	private static Log log = LogFactory.getLog(Providerdao.class);

	public static JSONObject queryProviderInfoAndRange(String code,
			String accountID) {
		log.info("accountID01101="+accountID);
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		short state = 1;

		JSONObject jo = new JSONObject();
		List<String> qualifiedSupplyRangeList = null;
		List<String> potentialSupplyRangeList = null;
		List<String> stopSupplyRangeList = null;

		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());


			/*pst = conn
					.prepareStatement("select cvenname from fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null) and (denddate is null or to_date(denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ))");*/
			pst = conn
					.prepareStatement("select cvenname from fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null)");

			pst.setString(1, code);

			rs = pst.executeQuery();

			if (rs.next()) {
				jo.append("companyName", rs.getString("cvenname"));
				log.info("accountID01101="+accountID);
				DBUtil.close(rs);
				DBUtil.close(pst);
				state = 2;
			/*	pst = conn
						.prepareStatement("select i.pk_invclass, i.cvendorclass from fy_supplier_vendor_invclass i, fy_supplier_vendor v where (i.dr='0' or i.dr is null) and i.pk_vendor=v.pk_vendor and v.cvencode = ? and trim(i.pk_corp) = ? and i.cvendorclass<>'3' and ( (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )))")
						*/
						pst = conn
						.prepareStatement("select i.pk_invclass, i.cvendorclass from fy_supplier_vendor_invclass i, fy_supplier_vendor v where (i.dr='0' or i.dr is null) and i.pk_vendor=v.pk_vendor and v.cvencode = ? and trim(i.pk_corp) = ? and i.cvendorclass<>'3'");

				pst.setString(1, code);
				pst.setString(2, accountID);

				rs = pst.executeQuery();
				while (rs.next()) {
					String cvendorclass = rs.getString(2);
					if ("1".equals(cvendorclass)) {
						if (qualifiedSupplyRangeList == null) {
							qualifiedSupplyRangeList = new ArrayList<String>();
						}
						qualifiedSupplyRangeList.add(rs.getString(1));
					} else if ("2".equals(cvendorclass)) {
						if (potentialSupplyRangeList == null) {
							potentialSupplyRangeList = new ArrayList<String>();
						}
						potentialSupplyRangeList.add(rs.getString(1));
					} else if ("3".equals(cvendorclass)) {
						if (stopSupplyRangeList == null) {
							stopSupplyRangeList = new ArrayList<String>();
						}
						stopSupplyRangeList.add(rs.getString(1));
					}
					state = 3;
				}
			}

		} catch (Exception e) {
			state = 4;
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		try {
			jo.put("state", state);
			jo.put("qualifiedSupplyRange",
					getRangeString(qualifiedSupplyRangeList));
			jo.put("potentialSupplyRange",
					getRangeString(potentialSupplyRangeList));
			// jo.put("stopSuppliers", getRangeString(stopSupplyRangeList));
		} catch (JSONException e) {
			e.printStackTrace();
		}

		// System.out.println(jo.toString());
		return jo;
	}

	public static String getProviderInfoAndRangeXMLByCode(String code,
			String accountID) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		JSONObject jo = new JSONObject();

		RootBean qualifiedSupplyRangeRootBean = new RootBean();
		RootBean potentialSupplyRangeRootBean = new RootBean();
		Map<String, List<NodeBean>> qualifiedSupplyRangeMap = new HashMap<String, List<NodeBean>>();
		Map<String, List<NodeBean>> potentialSupplyRangeMap = new HashMap<String, List<NodeBean>>();
		JSONObject jtgkJSON = null;
		short state = 1;

		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

		/*	pst = conn
					.prepareStatement("select cvenname from fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null) and (denddate is null or to_date(denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ) or cvencode = 'WFQJS01')");
*/

			pst = conn
					.prepareStatement("select cvenname from fy_supplier_vendor where cvencode = ? and (dr='0' or dr is null)");

			pst.setString(1, code);
			log.info("accountID001="+accountID+"code = "+code);
			rs = pst.executeQuery();
			log.info("accountID0111101="+accountID);
			log.info("rs.next()=");
			if (rs.next()) {
				jo.append("companyName", rs.getString("cvenname"));

				DBUtil.close(rs);
				DBUtil.close(pst);
				state = 2;
				System.out.println("accountID="+accountID);
				//log.info("accountID002="+accountID);

			/*	pst = conn
						.prepareStatement("select i.pk_invclass,f.cinvclassname, i.cvendorclass, f1.pk_invclass, f1.cinvclassname, f2.pk_invclass, f2.cinvclassname,f1.binvgroupdot from fy_supplier_vendor_invclass i left join  fy_supplier_vendor v on i.pk_vendor=v.pk_vendor left join fy_supplier_invclass f on i.pk_invclass = f.pk_invclass left join fy_supplier_invclass f1 on f.pk_invfather = f1.pk_invclass left join fy_supplier_invclass f2 on f1.pk_invfather = f2.pk_invclass where (i.dr='0' or i.dr is null) and trim(i.pk_corp) = ? and v.cvencode = ? and i.cvendorclass<>'3' and ( (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )))");
			*/
				if(accountID.equals("1004")){
					log.info("accountID1004--->step001");
					pst = conn
							.prepareStatement("select i.pk_invclass,f.cinvclassname, i.cvendorclass, f1.pk_invclass, f1.cinvclassname, f2.pk_invclass, f2.cinvclassname,f1.binvgroupdot from fy_supplier_vendor_invclass i left join  fy_supplier_vendor v on i.pk_vendor=v.pk_vendor left join fy_supplier_invclass f on i.pk_invclass = f.pk_invclass left join fy_supplier_invclass f1 on f.pk_invfather = f1.pk_invclass left join fy_supplier_invclass f2 on f1.pk_invfather = f2.pk_invclass where (i.dr='0' or i.dr is null) and (trim(i.pk_corp) = '1025' or trim(i.pk_corp) = '1004') and v.cvencode = ? and i.cvendorclass<>'3'");
					System.out.println("code="+code);
					log.info("accountID1004--->step002");
					//pst.setString(1, accountID);
					pst.setString(1, code);
				}else{
					pst = conn
							.prepareStatement("select i.pk_invclass,f.cinvclassname, i.cvendorclass, f1.pk_invclass, f1.cinvclassname, f2.pk_invclass, f2.cinvclassname,f1.binvgroupdot from fy_supplier_vendor_invclass i left join  fy_supplier_vendor v on i.pk_vendor=v.pk_vendor left join fy_supplier_invclass f on i.pk_invclass = f.pk_invclass left join fy_supplier_invclass f1 on f.pk_invfather = f1.pk_invclass left join fy_supplier_invclass f2 on f1.pk_invfather = f2.pk_invclass where (i.dr='0' or i.dr is null) and trim(i.pk_corp) = ? and v.cvencode = ? and i.cvendorclass<>'3'");

					pst.setString(1, accountID);
					pst.setString(2, code);
				}
//				pst = conn
//						.prepareStatement("select i.pk_invclass,f.cinvclassname, i.cvendorclass, f1.pk_invclass, f1.cinvclassname, f2.pk_invclass, f2.cinvclassname,f1.binvgroupdot from fy_supplier_vendor_invclass i left join  fy_supplier_vendor v on i.pk_vendor=v.pk_vendor left join fy_supplier_invclass f on i.pk_invclass = f.pk_invclass left join fy_supplier_invclass f1 on f.pk_invfather = f1.pk_invclass left join fy_supplier_invclass f2 on f1.pk_invfather = f2.pk_invclass where (i.dr='0' or i.dr is null) and trim(i.pk_corp) = ? and v.cvencode = ? and i.cvendorclass<>'3'");
//
//				pst.setString(1, accountID);
//				pst.setString(2, code);
				log.info("accountID1004--->step003");

				rs = pst.executeQuery();
				jtgkJSON = new JSONObject();
				while (rs.next()) {
					log.info("accountID1004--->step004");
					String cvendorclass = rs.getString(3);
					jtgkJSON.put(rs.getString(1), rs.getString(8));
					if ("1".equals(cvendorclass)) {

						// i.pk_invclass,f.cinvclassname, i.cvendorclass,
						// f1.pk_invclass, f1.cinvclassname, f2.pk_invclass,
						// f2.cinvclassname
						NodeBean bean1 = new NodeBean();
						bean1.setId(rs.getString(1));
						bean1.setText(rs.getString(2));
						String tempID = rs.getString(4);
						
						if (tempID != null && !"".equals(tempID)) {
							putNode(qualifiedSupplyRangeMap, tempID, bean1);
							NodeBean bean2 = new NodeBean();
							bean2.setId(tempID);
							bean2.setText(rs.getString(5));
							bean2.setDisabled(true);
							tempID = rs.getString(6);
							if (tempID != null && !"".equals(tempID)) {
								putNode(qualifiedSupplyRangeMap, tempID, bean2);
								NodeBean bean3 = new NodeBean();
								bean3.setId(tempID);
								bean3.setText(rs.getString(7));
								bean3.setDisabled(true);
								qualifiedSupplyRangeRootBean.addItem(bean3);
							} else {
								qualifiedSupplyRangeRootBean.addItem(bean2);
							}
						} else {
							qualifiedSupplyRangeRootBean.addItem(bean1);
						}
					} else if ("2".equals(cvendorclass)) {
						NodeBean bean1 = new NodeBean();
						bean1.setId(rs.getString(1));
						bean1.setText(rs.getString(2));
						String tempID = rs.getString(4);
						if (tempID != null && !"".equals(tempID)) {
							putNode(potentialSupplyRangeMap, tempID, bean1);
							NodeBean bean2 = new NodeBean();
							bean2.setId(tempID);
							bean2.setText(rs.getString(5));
							bean2.setDisabled(true);
							tempID = rs.getString(6);
							if (tempID != null && !"".equals(tempID)) {
								putNode(potentialSupplyRangeMap, tempID, bean2);
								NodeBean bean3 = new NodeBean();
								bean3.setId(tempID);
								bean3.setText(rs.getString(7));
								bean3.setDisabled(true);
								potentialSupplyRangeRootBean.addItem(bean3);
							} else {
								potentialSupplyRangeRootBean.addItem(bean2);
							}
						} else {
							potentialSupplyRangeRootBean.addItem(bean1);
						}
					}
					state = 3;
				}
				potentialSupplyRangeRootBean.addItems(potentialSupplyRangeMap);
				qualifiedSupplyRangeRootBean.addItems(qualifiedSupplyRangeMap);
			}

		} catch (Exception e) {
			state = 4;
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		try {
			jo.put("state", state);
			jo.put("qualifiedSupplyRange", qualifiedSupplyRangeRootBean
					.toXMLElement().asXML());
			jo.put("potentialSupplyRange", potentialSupplyRangeRootBean
					.toXMLElement().asXML());
			jo.put("jtgk", jtgkJSON);
			// jo.put("stopSuppliers", getRangeString(stopSupplyRangeList));
		} catch (JSONException e) {
			e.printStackTrace();
		}

		return jo.toString();
	}

	private static JSONArray getRangeString(List<String> list) {
		JSONArray ja = new JSONArray();
		if (list != null) {
			for (int i = 0, size = list.size(); i < size; i++) {
				ja.put(list.get(i));
			}
		}
		return ja;
	}
	
	private static void putNode(Map<String, List<NodeBean>> nodeMap,String parentPK, NodeBean node) {
		List<NodeBean> list = nodeMap.get(parentPK);
		if (list == null) {
			list = new ArrayList<NodeBean>();
			nodeMap.put(parentPK, list);
		}
		list.add(node);
	}
}
